USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0005_SUMA_INTERES]    Script Date: 11/28/2011 12:00:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0005_SUMA_INTERES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0005_SUMA_INTERES]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0005_SUMA_INTERES]    Script Date: 11/28/2011 12:00:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[WASP_M0005_SUMA_INTERES]
AS

BEGIN
		SELECT 
			SCCTA, 
			SCOPER, 
			SCSUC,	
			SCRUB, 
			J50PCCBNT, 
			J50PCCPS, 
			SUM (SCSDO) interes,
			'N' as Securitizado,
			NULL AS sec01ptcta
		into	dbo.interes 
		FROM 
			Fsd011 A (NOLOCK), 
			J50PCC B (NOLOCK)
		WHERE 
			--------- INTERES
			((SCRUB between '1161001000' AND '1161005000')  OR
			(SCRUB BETWEEN '1361001000' AND '1361005000')  OR
			(SCRUB between '1163001000' AND '1163005000')  OR
			(SCRUB BETWEEN '1363001000' AND '1363005000')  OR
			(SCRUB between '1164001000' AND '1164009000')  OR
			(SCRUB BETWEEN '1364001000' AND '1364009000')  OR
			(SCRUB between '1165001000' AND '1165009000')  OR
			(SCRUB BETWEEN '1365001000' AND '1370000000'))AND 
			SCRUB=J50PCCBNT AND SCSDO<>0 
		GROUP BY SCCTA, SCOPER,	SCSUC,SCRUB,J50PCCPS, J50PCCBNT
		union
		---------TOTAL CARTERA SECURITIZADA---------------
		SELECT 
			SCCTA, 
			SCOPER,
			SCSUC,	
			SCRUB, 
			J50PCCBNT, 
			J50PCCPS, 
			SUM (SCSDO) interes,
			'S' as Securitizado,
			sec01ptcta

		FROM 
			Fsd011 A (NOLOCK), 
			FSEC10 B (NOLOCK), 
			J50PCC C (NOLOCK)
		WHERE
		-- Interés
			((SCRUB BETWEEN '8861001000' AND '8863000000')  OR
			(SCRUB BETWEEN '8863001000' AND '8863004400')  OR
			(SCRUB BETWEEN '8864001000' AND '8865000000')  OR
			(SCRUB BETWEEN '8865001000' AND '8870000000') OR
			(scrub between '8961001000' and '8963000000') or
			(scrub between '8963001000' and '8964000000') or
			(scrub between '8964001000' and '8965000000' ) or
			(scrub between '8965001000' and '8970000000'))AND 
			A.SCCTA = B.Sec10AoCta AND
			A.SCOPER = B.Sec10AoOpe AND
			A.SCSUC = B.Sec10AoSuc AND
			--B.sec10stspt = 9 AND
			A.SCRUB=C.J50PCCBNT AND 
			B.sec01ptcta = C.J50PCCOPE AND
			A.SCSDO<>0 
			
		GROUP BY SCCTA, SCOPER, Scsuc,SCRUB,J50PCCPS, J50PCCBNT,sec01ptcta
END
GO

